package DBLayer;

import java.sql.*;
import java.util.ArrayList;
import ModelLayer.*;

public class DBSalesOrder {
	private  Connection con;
	/** Creates a new instance of DBSalesOrder */
	public DBSalesOrder() {
		con = DbConnection1.getInstance().getDBcon();
	}

	//Implements the methods from the interface
	// get all SalesOrders
	public ArrayList<SalesOrder> getAllSalesOrders(boolean retriveAssociation)
	{
		return miscWhere("", retriveAssociation);
	}
	//get one SalesOrder having the salesID
	public SalesOrder findSalesOrder(int salesID, boolean retriveAssociation)
	{   String wClause = "  salesID = '" + salesID + "'";
	return singleWhere(wClause, retriveAssociation);
	}
	//insert a new SalesOrder
	
	public int insertSalesOrder(SalesOrder order) throws Exception
	{  //call to get the next id number
		int nextSalesID = GetMax.getMaxId("Select max(salesID) from SalesOrder");
		nextSalesID = nextSalesID + 1;
		System.out.println("next salesID = " +  nextSalesID);

		int rc = -1;
		String query="INSERT INTO SalesOrder(salesID, totalPrice, saleDate, diliveryStatus, " +
				"diliveryDate, customerID, empID)  VALUES('"+
				nextSalesID + "','" +
				order.getPrice()  + "','"  +
				order.getSaleDate()  + "','"  +				
				order.getDeliveryStatus()  + "','"  +
				order.getDeliveryDate()  + "','"  +
				order.getCustomer().getSalesID()  + "','"  +
				order.getEmployee().getSalesID() + ")";

		System.out.println("insert : " + query);
		try{ // insert new SalesOrder 
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			rc = stmt.executeUpdate(query);
			stmt.close();
		}//end try
		catch(SQLException ex){
			System.out.println("SalesOrder ikke oprettet");
			throw new Exception ("SalesOrder is not inserted correct");
		}
		return(rc);
	}

	public int updateSalesOrder(SalesOrder order)
	{
		SalesOrder orderObj  = order;
		int rc=-1;

		String query="UPDATE SalesOrder SET "+
				"salesDate ='"+ orderObj.getSaleDate()+"', "+
				"totalPrice ='"+ orderObj.getPrice() + "', " +
				"diliveryStatus ='"+ orderObj.getDeliveryStatus() + "', " +
				"diliveryDate ='"+ orderObj.getDeliveryDate() + "' " +
				"customer ='"+ orderObj.getCustomer().getSalesID() + "' " +
				"employee ='"+ orderObj.getEmployee().getSalesID() + "' " +
				" WHERE salesID = '"+ orderObj.getSalesID() + "'";
		System.out.println("Update query:" + query);
		try{ // update SalesOrder
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			rc = stmt.executeUpdate(query);

			stmt.close();
		}//end try
		catch(Exception ex){
			System.out.println("Update exception in SalesOrder db: "+ex);
		}
		return(rc);
	}

	public int delete(int salesID)
	{
		int rc=-1;

		String query="DELETE FROM SalesOrder WHERE salesID = '" +
				salesID + "'";
		System.out.println(query);
		try{ // delete from SalesOrder
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			rc = stmt.executeUpdate(query);
			stmt.close();
		}//end try	
		catch(Exception ex){
			System.out.println("Delete exception in SalesOrder db: "+ex);
		}
		return(rc);
	}

	//private methods
	//michWere is used whenever we want to select more than one SalesOrder


	private ArrayList<SalesOrder> miscWhere(String wClause, boolean retrieveAssociation)
	{
		ResultSet results;
		ArrayList<SalesOrder> list = new ArrayList<SalesOrder>();	

		String query =  buildQuery(wClause);

		try{ // read the SalesOrder from the database
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			results = stmt.executeQuery(query);


			while( results.next() ){
				SalesOrder salObj = new SalesOrder();
				salObj = buildSalesOrder(results);	
				list.add(salObj);	
			}//end while
			stmt.close();     
			if(retrieveAssociation)
			{   //The customer is to be build as well
				for(SalesOrder salObj : list){
					 int customerID = salObj.getCustomer().getSalesID();
					 SalesOrder custOrder = singleWhere(" salesID = '" + customerID + "'",false);
					 salObj.setCustomer(custOrder);
                     System.out.println("Supervisor is seleceted");
				}
			}//end if   

		}//end try	
		catch(Exception e){
			System.out.println("Query exception - select: "+e);
			e.printStackTrace();
		}
		return list;
	}

	//Singlewhere is used when we only select one SalesOrder 	
	private SalesOrder singleWhere(String wClause, boolean retrieveAssociation)
	{
		ResultSet results;
		SalesOrder salObj = new SalesOrder();

		String query =  buildQuery(wClause);
		System.out.println(query);
		try{ // read the SalesOrder from the database
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			results = stmt.executeQuery(query);

			if( results.next() ){
				salObj = buildSalesOrder(results);
				//Association is to be build
				stmt.close();
				if(retrieveAssociation)
				{   //The customer is to be build as well
					int customerID = salObj.getCustomer().getSalesID();
					 SalesOrder custOrder = singleWhere(" salesID = '" + customerID + "'",false);
					 salObj.setCustomer(custOrder);
                    System.out.println("Supervisor is seleceted");
				}
			}
			else{ //no SalesOrder was found
				salObj = null;
			}
		}//end try	
		catch(Exception e){
			System.out.println("Query exception: "+e);
		}
		return salObj;
	}
	//method to build the query
	private String buildQuery(String wClause)
	{
		String query="SELECT * FROM SalesOrder";

		if (wClause.length()>0)
			query=query+" WHERE "+ wClause;

		return query;
	}
	//method to build an SalesOrder object
	private SalesOrder buildSalesOrder(ResultSet results)
	{   
		SalesOrder orderObj = new SalesOrder();
		try{ // the columns from the table salesOrder  are used
			orderObj.setSalesID(results.getInt("salesID"));
			orderObj.setPrice(results.getDouble("totalPrice"));
			orderObj.setSalesDate(results.getString("saleDate"));
			orderObj.setDeliveryStatus(results.getString("diliveryStatus"));
			orderObj.setDeliveryDate(results.getString("diliveryDate"));
			orderObj.setCustomer(new SalesOrder(results.getInt("customerID")));
			orderObj.setEmployee(new SalesOrder(results.getInt("empID")));
		}
		catch(Exception e)
		{
			System.out.println("error in building the SalesOrder object");
		}
		return orderObj;
	}

}
